package com.situ.dao;

import java.sql.SQLException;
import java.util.List;

import com.situ.entity.User;
import com.situ.util.DataSourceUtil;
import com.situ.util.StringUtil;

public class UserDao {

	public int addUser(User user) {
		String sql = "insert into user(username,password,name,gender,birthday,email) values(?,?,?,?,?,?)";
		try {
			return DataSourceUtil.executeUpdate(sql, user.getUsername(), user.getPassword(), user.getName(), user.getGender(), user.getBirthday(), user.getEmail());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public List<User> check(String username, String password) {
		String sql = "select * from user where username=? and password=?";

		try {
			return DataSourceUtil.executeQuery(sql, User.class, username, password);
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<User> selectUsers(String username, String name, Integer a, Integer b) {
		String sql = "select * from user ";

		if (!StringUtil.isEmpty(username) || !StringUtil.isEmpty(name)) {
			sql += " where ";
		}

		if (!StringUtil.isEmpty(username)) {
			sql += " instr(username,'" + username + "')>0";
		}
		if (!StringUtil.isEmpty(name)) {
			if (!StringUtil.isEmpty(username)) {
				sql += " and ";
			}
			sql += " instr(name,'" + name + "')>0";
		}
		sql += " limit ?,?";
		try {
			return DataSourceUtil.executeQuery(sql, User.class, a, b);
		} catch (Exception e) {

		}
		return null;
	}

	public Integer count(String username, String name) {
		String sql = "select count(id) from user ";
		if (!StringUtil.isEmpty(username) || !StringUtil.isEmpty(name)) {
			sql += " where ";
		}
		if (!StringUtil.isEmpty(username)) {
			sql += " instr(username,'" + username + "')>0";
		}
		if (!StringUtil.isEmpty(name)) {
			if (!StringUtil.isEmpty(username)) {
				sql += " and ";
			}
			sql += " instr(name,'" + name + "')>0";
		}
		try {
			return DataSourceUtil.executeCount(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	public List<User> selectAll(Integer a, Integer b) {

		// 共100 条 每页10条 查第2页
		// limit 2,10
		// limit 1,10
		// limit 10,10
		// limit 2,11
		// limit 0,10
		// limit 10,20

		String sql = "select * from user limit ?,?";
		try {
			return DataSourceUtil.executeQuery(sql, User.class, a, b);
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void del(String id) {
		String sql = "delete from user where id = ?";
		try {
			DataSourceUtil.executeUpdate(sql, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<User> selectUserById(String id) {
		String sql = "select * from user where id = ?";

		try {
			return DataSourceUtil.executeQuery(sql, User.class, id);
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public void update(User user) {
		String sql = "update user set username=?,name=?,age=?,email=? where id=?";

		try {
			DataSourceUtil.executeUpdate(sql, user.getUsername(), user.getName(), user.getAge(), user.getEmail(), user.getId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
